# mssql数据同步至sqlite
import sqlite3
import collections
import pymssql
##################### MSSSQL 数据库信息 ####################
server = 'localhost'
user = 'sa'
password = '123456'
dbname = 'myTest'
#################### Sqlite 数据库信息 #####################
sqlpath = 'downdata.db'

# 读取Sqlite 数据


def ReadSqliteData(strsql: str):
    conn = sqlite3.connect(sqlpath)
    cursor = conn.cursor()  # 获取光标
    try:
        cursor.execute(strsql)  # 执行SQL
        rows = cursor.fetchall()  # 获取所有数据
        conn.commit()
        return rows
    except AttributeError as e:
        print(e)  # 输出异常
        return []
    finally:
        conn.close()  # 数据库操作完成后要关闭连接

# 读取MSSQL数据库数据


def ReadMssqlData(sqls: str):
    # 连接数据库 (服务器地址,登录数据库账号,登录数据库账号密码,指定数据库)
    conn = pymssql.connect(server, user, password, dbname, charset='utf8')
    cursor = conn.cursor()  # 获取光标
    try:
        cursor.execute(sqls)  # 执行SQL
        rows = cursor.fetchall()  # 获取所有数据
        conn.commit()
        return rows
    except AttributeError as e:
        print(e)  # 输出异常
        return []
    finally:
        conn.close()  # 数据库操作完成后要关闭连接
# 写入数据
    # 保存数据


def WriteToMssql(sql: str, params: list):
    # 连接数据库 (服务器地址,登录数据库账号,登录数据库账号密码,指定数据库)
    conn = pymssql.connect(server, user, password, dbname, charset='utf8')
    cursor = conn.cursor()  # 获取光标
    try:
        cursor.executemany(sql, params)
        conn.commit()
    except Exception as e:
        print('保存出现异常')
        print(e)  # 输出异常
    finally:
        conn.close()  # 数据库操作完成后要关闭连接


def DealPlayUrl():
    zsql = 'select * from OkPlay '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into OkPlay(Id,ParentId,Title,PlayUrl)values(%d,%s,%s,%s) '
    print('OkPlay')
    WriteToMssql(isql, zlist)
    zsql = 'select * from OkMu38 '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into OkMu38(Id,ParentId,Title,PlayUrl)values(%d,%s,%s,%s) '
    print('OkMu38')
    WriteToMssql(isql, zlist)
    zsql = 'select * from ZuiDaPlay '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into ZuiDaPlay(Id,ParentId,Title,PlayUrl)values(%d,%s,%s,%s) '
    print('ZuiDaPlay')
    WriteToMssql(isql, zlist)
    zsql = 'select * from ZuiDaMu38 '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into ZuiDaMu38(Id,ParentId,Title,PlayUrl)values(%d,%s,%s,%s) '
    print('ZuiDaMu38')
    WriteToMssql(isql, zlist)


def DealDownUrl():
    zsql = 'select * from OkDown '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into OkDown(Id,ParentId,Title,DownUrl)values(%d,%s,%s,%s) '
    print('OkDown')
    WriteToMssql(isql, zlist)
    zsql = 'select * from ZuiDaDown '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into ZuiDaDown(Id,ParentId,Title,DownUrl)values(%d,%s,%s,%s) '
    print('ZuiDaDown')
    WriteToMssql(isql, zlist)


def DealDetail():
    zsql = 'select * from OkDetail '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into OkDetail(Id,ParentId,Remark,MingChen,BieMing,DaoYan,ZhuYan,LeiXing,DiQu,YuYan,ShangYing,PianChang,GengXing,Juqing,PingFen,ZongPin,PinFen,ImgUrl,ImgAlt)values(%d,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) '
    print('OkDetail')
    WriteToMssql(isql, zlist)
    zsql = 'select * from ZuiDaDetail '
    zlist = ReadSqliteData(zsql)
    isql = 'insert into ZuiDaDetail(Id,ParentId,Remark,MingChen,BieMing,DaoYan,ZhuYan,LeiXing,DiQu,YuYan,ShangYing,PianChang,GengXing,Juqing,PingFen,ZongPin,PinFen,ImgUrl,ImgAlt)values(%d,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) '
    print('ZuiDaDetail')
    WriteToMssql(isql, zlist)


def DealSource():
    zsql = "select Id,ReqUrl,VideoName,VideoType,TypeCode,cast(UpdateTime as char(10)) as UpdateTime ,IsEnd,IsDeal from OkSource "
    zlist = ReadSqliteData(zsql)
    isql = 'insert into OkSource(Id,ReqUrl,VideoName,VideoType,TypeCode,UpdateTime,IsEnd,IsDeal)values(%d,%s,%s,%s,%s,%s,%s,%s)'
    print('OkSource')
    WriteToMssql(isql, zlist)
    zsql = "select Id,ReqUrl,VideoName,VideoType,TypeCode,cast(UpdateTime as char(10)) as UpdateTime ,IsEnd,IsDeal from ZuiDaSource "
    zlist = ReadSqliteData(zsql)
    isql = 'insert into ZuiDaSource(Id,ReqUrl,VideoName,VideoType,TypeCode,UpdateTime,IsEnd,IsDeal)values(%d,%s,%s,%s,%s,%s,%s,%s)'
    print('ZuiDaSource')
    WriteToMssql(isql, zlist)


# 导入数据
# 播放链接
DealPlayUrl()
# 下载链接
DealDownUrl()
# 详情
DealDetail()
# 采集链接
DealSource()
